﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;


namespace KPIS.GERP.GIMS.DAL
{
    public sealed class DeliveryNoteItemExtraDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DeliveryNoteItemExtraDAL()
        {
        
        }

        public IList<DeliveryNoteItemExtraInfo> GetDeliveryNoteItemExtraWithParent(int dn_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DeliveryNoteItemExtraInfo> infoList = new List<DeliveryNoteItemExtraInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@dn_seq", dn_seq);

                strSQL = "SELECT dn.*"
                            + ", ri.ICM_RES_ITEM_NAME"
                        + " FROM ICM_DELIVERY_NOTE_ITEM_EXTRA dn"
                            + " INNER JOIN ICM_MAS_RESOURCE_ITEM ri ON dn.ICM_RES_ITEM_SEQ = ri.ICM_RES_ITEM_SEQ"
                        + " WHERE dn.ICM_DN_SEQ = @dn_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DeliveryNoteItemExtraInfo info = new DeliveryNoteItemExtraInfo();
                        ResourceMasterItemInfo infoResItem = new ResourceMasterItemInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_DN_ITEM_EXTRA_SEQ"]);
                        infoResItem.Seq = Convert.ToInt32(dr[i]["ICM_RES_ITEM_SEQ"]);
                        infoResItem.Name = dr[i]["ICM_RES_ITEM_NAME"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ITEM_QTY"]) ? null : (Nullable<int>)Convert.ToInt32(dr[i]["ITEM_QTY"]));
                        info.Price = (Convert.IsDBNull(dr[i]["ITEM_PRICE"]) ? null : (Nullable<double>)Convert.ToDouble(dr[i]["ITEM_PRICE"]));
                        info.Descr = dr[i]["ITEM_DESCR"].ToString();
                        info.Remark = dr[i]["ITEM_REMARK"].ToString();

                        info.InfoResItem = infoResItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void Insert(IList<DeliveryNoteItemExtraInfo> infoList, Nullable<int> dn_seq, DeliveryNoteInfo info, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(8);

                    dbManagerTrans.AddParameters(0, "@dn_seq", dn_seq);
                    dbManagerTrans.AddParameters(1, "@item_seq", infoList[i].InfoResItem.Seq);
                    dbManagerTrans.AddParameters(2, "@item_descr", infoList[i].Descr);
                    dbManagerTrans.AddParameters(3, "@item_qty", infoList[i].Qty);
                    dbManagerTrans.AddParameters(4, "@item_price", infoList[i].Price);
                    dbManagerTrans.AddParameters(5, "@item_remark", infoList[i].Remark);
                    dbManagerTrans.AddParameters(6, "@created_by", info.InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(7, "@created_when", info.InfoRecord.Created_when);

                    strSQL = "INSERT INTO ICM_DELIVERY_NOTE_ITEM_EXTRA("
                                + "ICM_DN_SEQ"
                                + ", ICM_RES_ITEM_SEQ"
                                + ", ITEM_DESCR"
                                + ", ITEM_QTY"
                                + ", ITEM_REMARK"
                                + ", ITEM_PRICE"
                                + ", CREATED_BY"
                                + ", CREATED_WHEN"
                                + ") "
                            + " VALUES (@dn_seq"
                                + ", @item_seq"
                                + ", @item_descr"
                                + ", @item_qty"
                                + ", @item_remark"
                                + ", @item_price"
                                + ", @created_by"
                                + ", @created_when"
                                + ")";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void Insert(IList<DeliveryNoteItemExtraInfo> infoList)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManager.CreateParameters(8);

                    dbManager.AddParameters(0, "@dn_seq", infoList[i].InfoDn.Seq);
                    dbManager.AddParameters(1, "@item_seq", infoList[i].InfoResItem.Seq);
                    dbManager.AddParameters(2, "@item_descr", infoList[i].Descr);
                    dbManager.AddParameters(3, "@item_qty", infoList[i].Qty);
                    dbManager.AddParameters(4, "@item_price", infoList[i].Price);
                    dbManager.AddParameters(5, "@item_remark", infoList[i].Remark);
                    dbManager.AddParameters(6, "@created_by", infoList[i].InfoRecord.Created_by);
                    dbManager.AddParameters(7, "@created_when", infoList[i].InfoRecord.Created_when);

                    strSQL = "INSERT INTO ICM_DELIVERY_NOTE_ITEM_EXTRA("
                                + "ICM_DN_SEQ"
                                + ", ICM_RES_ITEM_SEQ"
                                + ", ITEM_DESCR"
                                + ", ITEM_QTY"
                                + ", ITEM_REMARK"
                                + ", ITEM_PRICE"
                                + ", CREATED_BY"
                                + ", CREATED_WHEN"
                                + ") "
                            + " VALUES (@dn_seq"
                                + ", @item_seq"
                                + ", @item_descr"
                                + ", @item_qty"
                                + ", @item_remark"
                                + ", @item_price"
                                + ", @created_by"
                                + ", @created_when"
                                + ")";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteFromDB(IList<DeliveryNoteItemExtraInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM ICM_DELIVERY_NOTE_ITEM_EXTRA"
                    + " WHERE ICM_DN_ITEM_EXTRA_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
